Table Functions
| Name | Description | Example |
|---|---|---|
| index(<table>, <row>,<column>) | Returns the value of cell at <column>, <row> in <table>. | index("Tranche: IRR Table", "Tranche: Row Number", 1) |
| interpolate(<table>, <value>, <searchColumn>, <returnColumn>, [<extrapolate>]) | Looks up <value> in column <searchColumn> of <table> and returns a linearly interpolated value from <returnColumn>. All values in <searchColumn> must be either ascending or descending. The optional <extrapolate> parameter is a Boolean that determines if the function must extrapolate linearly outside the provided range. |
interpolate("Interpolation", "Tranche: Value Array", 1, 2)
In the following table, for "Tranche: Value Array" = 0.5 the example above returns the value 3.5 = 3 + (0.5-0.4)/(0.8-0.4) * (3-5).
 |
| interpolate2D(<table>, <x>, <y>) |
Performs a bilinear or 2-dimensional interpolation in <table>, using <x> and <y> as indices. The first row and the first column of the table are used to represent the X and Y axes respectively, and their values of each should be ascending; the cell at position 1,1 is ignored.
<x> and <y> can also be arrays. In this case the function will return an array too.
|
interpolate2D("Interpolate2D", 1.5, 1.5)
In the following table, the example above returns the value 4.5:
 |
| tableLookup(<table>, <value>, <searchColumn>, <returnColumn>) | Looks up <value> in column <searchColumn> of <table> and returns the corresponding lower value from <returnColumn>. Values in <searchColumn> must be ascending. | tableLookup("Interpolate2D", "Tranche: Value Array", 1, 2) In the Interpolate2D table (see above), for "Tranche: Value Array" = 1.5 the function returns 1. |
| tableRollup(<table>, <value>, <searchColumn>, <factorColumn>) | Looks up <value> in column <searchColumn> of <table> and returns the "Rollup" value. Rollup is calculated as the sum of the values in <searchColumn> minus the value in the previous row in <searchColumn> multiplied by the value in the same (previous) row of <factorColumn>, for all rows below the one where <searchColumn> is less than <value>. If <value> ≤ the minimum value in <searchColumn> the value "0" is returned. |
tableRollup("Rollup", "Tranche: Value Array", 1, 2)
In the following table, for "Tranche: Value Array" = 0.5 the example above returns the value 0.9 = (0.2-0)*1 + (0.4-0.2)*2 + (0.5-0.4)*3.
 |
| vLookup(<table>, <value>, <returnColumn>) | Looks up <value> in first column of <table> and returns the corresponding lower value from <returnColumn>. The values in the first column must be ascending. | vLookup("Rollup", "Tranche: Value Array", 2) In the Rollup table (see above), for "Tranche: Value Array" = 0.5 the function returns 3. |
The vLookup function has two modes, depending on whether the first column is in ascending order or not. If the first column is in ascending order, then the function looks for the first entry that is greater than or equal to the <value> argument. If all entries are smaller than <value>, the first row is selected. When the first column is not in ascending order, the search proceeds in exact matching mode. If no entry in the first column matches the argument, the function evaluates to undefined.
The vLookup function accepts arrays as arguments: you can enter a single value for <value> and an array for <returnColumn>, or arrays for both. When so defined, the value to look up will be correlative to each simulation period.
Table functions can reference column names instead of column indexes, e.g.:
interpolate("Pvr: Depth Cost Table", 150 [ft], 'Depth', 'Cost')
Note that column names are case-sensitive.
If you change a column name in the table (using Edit Properties), all FML expressions referencing that column by name will be automatically updated provided these column names are literal strings. If the column name is the result of evaluating a FML Text expression it will no be updated.